package cn.explink.dop.dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import cn.explink.dop.entity.vo.OrderVO;
import cn.explink.dop.enums.OrderStatusEnum;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;

/**
 *
 * @author songkaojun 2015年3月16日
 */
@Repository("orderDao")
public class OrderDao extends GenericBaseCommonDao<OrderVO, String> {
	/**
	 * 添加数据
	 *
	 * @param res
	 *            添加对象
	 * @return 添加成功之后对象
	 */
	
	public OrderVO add(final OrderVO orderVO) {
		return (OrderVO) this.save(orderVO);
	}

	/*
	 *
	 */
	public String queryDeliveryManName(String id) {
		String sql = "SELECT del_name FROM bd_deliveryman WHERE id =  '" + id + "'";
		Object name = this.getSession().createSQLQuery(sql).list();
		int length = name.toString().length();
		return name.toString().substring(1, length - 1);
	}

	/**
	 * 按照实体查询，无分页
	 *
	 * @param res
	 *            查询实体
	 * @return 查询列表
	 */
	public List<OrderVO> finds(final OrderVO orderVO) {
		StringBuffer allFromSql = new StringBuffer("from OrderVO ");
		StringBuffer whereSql = this.getWhereSql(orderVO);

		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery(this.processWhere(allFromSql.append(whereSql).toString())).list();
		return orderList;
	}

	public List<OrderVO> getByIdList(List<String> orderIdList) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<String> set = new TreeSet<String>();
		for (String orderId : orderIdList) {
			set.add("'" + orderId + "'");
		}
		sql.append(" ").append("where id in (").append(StringUtils.join(set.toArray(), ",")).append(")");

		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery((sql.toString())).list();
		return orderList;
	}

	public List<OrderVO> getByStatusAndDate(String deliveryManId, List<Integer> statusList, Date startDate, Date endDate) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		if (StringUtils.isNotEmpty(deliveryManId)) {
			sql.append(" and deliveryman_id='" + deliveryManId + "'");
		}
		if ((null != startDate) && (null != endDate)) {
			SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
			sql.append(" and date_format(status_change_time,'%Y-%m-%d') between '" + sd.format(startDate) + "' and '" + sd.format(endDate) + "'");
		}

		sql.append(" order by complete_Time desc ");
		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery(sql.toString()).list();
		return orderList;
	}

	public List<OrderVO> getByStatusAndAskSendTime(List<Integer> statusList, Date startDate, Date endDate) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		if ((null != startDate) && (null != endDate)) {
			SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			sql.append(" and date_format(ask_send_time,'%Y-%m-%d %H:%i:%s') between '" + sd.format(startDate) + "' and '" + sd.format(endDate) + "'");
		}
		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery(sql.toString()).list();
		return orderList;
	}
	/**
	 * 
	* @Title: getOrderByStatusAndDistributiontime 
	* @Description: 自动分配找出满足分配要求的订单（未分配、被配送员拒接、配送员在规定时间内未接） 
	* @author 小强
	* @param @param statusList
	* @param @param startDate
	* @param @param endDate
	* @param @return    设定文件 
	* @return List<OrderVO>    返回类型 
	* @throws
	 */
	public List<OrderVO> getOrderByStatusAndDistributiontime(List<Integer> statusList, Integer AllocatedStatus, double redistributionTime) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		sql.append(" ").append("or (status="+AllocatedStatus+" and (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(distribute_time))/60>"+ redistributionTime).append(")");
		sql.append(" ").append("and ask_send_time is not null");
		
		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery(sql.toString()).list();
		return orderList;
	}
	
	public List<OrderVO> getAllByStatus(List<Integer> statusList) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");

		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery((sql.toString())).list();
		return orderList;
	}

	public Integer getCountByStatus(String deliveryManId, List<Integer> statusList) {
		StringBuffer sql = new StringBuffer("select count(id) from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		if (StringUtils.isNotEmpty(deliveryManId)) {
			sql.append(" and deliveryman_id='" + deliveryManId + "'");
		}

		Integer count = ((Long) this.getSession().createQuery((sql.toString())).uniqueResult()).intValue();
		return count;
	}

	/**
	 * 按照实体获取分页数据
	 *
	 * @param baseInfo
	 *            查询实体
	 * @param pageNum
	 *            当前页
	 * @param pageSize
	 *            当前页记录条数
	 * @return 查询结果集
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> findByPage(final OrderVO orderVO, String deliveryManName, final int pageNum, final int pageSize) {

		StringBuffer allFromSql = new StringBuffer("from OrderVO where 1=1 ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from OrderVO where 1=1 ");
		StringBuffer whereSql = new StringBuffer();
		/**
		 * 条件参数
		 */
		List<Object> param = new ArrayList<Object>();
		if (StringUtils.isNotBlank(orderVO.getCode())) {
			whereSql.append(" and code like ? ");
			param.add("%" + orderVO.getCode() + "%");
		}
		if (StringUtils.isNotBlank(deliveryManName)) {
			whereSql.append(" and deliveryMan.delName like ? ");
			param.add("%" + deliveryManName + "%");
		}
		if (orderVO.getStatus() != 0) {
			whereSql.append(" and status=? ");
			param.add(orderVO.getStatus());
		}
		if (StringUtils.isNotBlank(orderVO.getCustName())) {
			whereSql.append(" and cust_name like ? ");
			param.add("%" + orderVO.getCustName() + "%");
		}
		if (orderVO.getPayMethod() != 0) {
			whereSql.append(" and pay_method=? ");
			param.add(orderVO.getPayMethod());
		}
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
		if (orderVO.getCreateDate() != null) {
			whereSql.append(" and date_format(create_date,'%Y-%m-%d')='").append(sd.format(orderVO.getCreateDate())).append("'");
		}
		if (orderVO.getDeliveryTime() != null) {
			whereSql.append(" and date_format(delivery_time,'%Y-%m-%d')='").append(sd.format(orderVO.getDeliveryTime())).append("'");
		}
		if (orderVO.getAskSendTime() != null) {
			whereSql.append(" and date_format(ask_send_time,'%Y-%m-%d')='").append(sd.format(orderVO.getAskSendTime())).append("'");
		}

		Map<String, Object> map = new HashMap<String, Object>();
		Query queryCount = this.getSession().createQuery(countFromSql.append(whereSql).toString());
		String sql = allFromSql.append(whereSql).toString();
		Query query = this.getSession().createQuery(sql);
		/**
		 * 遍历条件参数
		 */
		for (int i = 0; i < param.size(); i++) {
			queryCount.setParameter(i, param.get(i));
			query.setParameter(i, param.get(i));
		}
		query.setFirstResult((pageNum - 1) * pageSize);
		query.setMaxResults(pageSize);
		Integer count = ((Long) queryCount.uniqueResult()).intValue();
		map.put("count", count);
		List<OrderVO> orderList = query.list();
		map.put("list", orderList);
		return map;
	}

	private StringBuffer getWhereSql(final OrderVO orderVO) {
		StringBuffer whereSql = new StringBuffer("");
		if (null != orderVO) {
			whereSql.append("where");
		}
		if (StringUtils.isNotBlank(orderVO.getCode())) {
			whereSql.append(" and code='").append(orderVO.getCode()).append("'");
		}
		if (StringUtils.isNotBlank(orderVO.getDeliveryManName())) {
			whereSql.append(" and deliveryMan.delName='").append(orderVO.getDeliveryManName()).append("'");
		}
		if (orderVO.getStatus() != 0) {
			whereSql.append(" and status=").append(orderVO.getStatus()).append("");
		}
		if (StringUtils.isNotBlank(orderVO.getCustName())) {
			whereSql.append(" and cust_name='").append(orderVO.getCustName()).append("'");
		}
		if (orderVO.getCreateDate() != null) {
			SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
			whereSql.append(" and date_format(create_date,'%Y-%m-%d')='").append(sd.format(orderVO.getCreateDate())).append("'");
		}
		if (orderVO.getPayMethod() != 0) {
			whereSql.append(" and pay_method=").append(orderVO.getPayMethod()).append("");
		}
		return whereSql;
	}

	private String processWhere(String sqlStr) {
		if (sqlStr.contains("where and")) {
			sqlStr = sqlStr.replace("where and", "where");
		} else if (sqlStr.contains("where")) {
			sqlStr = sqlStr.replace("where", "");
		}
		return sqlStr;
	}

	public List<OrderVO> getByStatusAndDate(String deliveryManId, List<Integer> statusList, Date startDate, Date endDate, int pageIndex, int pageCount) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		if (StringUtils.isNotEmpty(deliveryManId)) {
			sql.append(" and deliveryman_id='" + deliveryManId + "'");
		}
		if ((null != startDate) && (null != endDate)) {
			sql.append(" and status_change_time between " + startDate + " and " + endDate);
		}

		@SuppressWarnings("unchecked")
		List<OrderVO> orderList = this.getSession().createQuery(sql.toString()).setFirstResult((pageIndex - 1) * pageCount).setMaxResults(pageCount).list();
		return orderList;
	}

	/**
	 *
	 * @Title: getTheDayUpMoney
	 * @Description: 当天需要缴纳多少钱和已缴纳多少钱 ：需要缴纳--->当天实收款总减当天取餐付款总数 ：已缴纳--->状态为已成功的
	 * @author 孟凡岭
	 * @param @param Id
	 * @param @return
	 * @return BigDecimal 返回类型
	 * @throws
	 */
	public Map<String, Object> getTheDayUpMoney(String Id) {
		// TODO Auto-generated method stub

		StringBuffer sql = new StringBuffer("select count(*) as count,IFNULL(SUM(o.Order_Total_Price)-SUM(o.Take_Meal_Price),0) as money ");
		sql.append(" from or_order o where o.deliveryman_id=? ");
		sql.append(" and DATE_FORMAT(o.status_change_time,'%Y-%m-%d')='" + this.getSystemDateForStr("yyyy-MM-dd") + "'");
		sql.append(" and o.pay_method=2 and o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue());
		Object[] param = new Object[1];
		param[0] = Id;
		Map<String, Object> map = this.getJdbcTemplate().queryForMap(sql.toString(), param);
		Map<String, Object> map2 = this.getJdbcTemplate().queryForMap(sql.toString(), param);
		map.put("alreadyCount", map2.get("count"));
		map.put("alreadyMoney", map2.get("money"));
		return map;
	}

	/**
	 *
	 * @Title: getRealityIncome
	 * @Description: 获取当天实际收入
	 * @author 孟凡岭
	 * @param @param userId
	 * @param @return
	 * @return BigDecimal 返回类型
	 * @throws
	 */
	public BigDecimal getRealityIncome(String Id) {
		// TODO Auto-generated method stub
		Map<String, Object> map = this.getTheDayUpMoney(Id);
		BigDecimal dec = new BigDecimal(0);
		if (map.get("money") != null) {
			dec = new BigDecimal(map.get("money").toString());
			String sql = "select value from bd_busi_param where name='提成'";
			String value = this.getJdbcTemplate().queryForMap(sql).get("value").toString();
			BigDecimal dect = new BigDecimal(value);
			dec = dec.multiply(dect);
		}
		return dec.setScale(2);
	}

	/**
	 *
	 * @Title: getMonthIncome
	 * @Description: 获取当月实际收入
	 * @author 孟凡岭
	 * @param @param userId
	 * @param @return
	 * @return BigDecimal 返回类型
	 * @throws
	 */
	public BigDecimal getMonthIncome(String userId) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * @param pageSize
	 * @param pageNum
	 *
	 * @Title: getDaySuccessOrderList
	 * @Description: 获取当天完成订单列表，支付方式为现金，按修改时间排序
	 * @author 孟凡岭
	 * @param @param manId
	 * @param @return
	 * @return List<OrderVO> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public List<OrderVO> getDaySuccessOrderList(String manId, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("from OrderVO o where status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + " and payMethod=2 and deliveryman_id=:id ");
		sql.append(" and DATE_FORMAT(status_change_time,'%Y-%m-%d')='" + this.getSystemDateForStr("yyyy-MM-dd") + "'");
		sql.append(" order by complete_Time desc ");
		Query query = this.getSession().createQuery(sql.toString());
		query.setParameter("id", manId);
		query.setFirstResult((pageNum - 1) * pageSize);
		query.setMaxResults(pageSize);
		return query.list();
	}

	/**
	 *
	 * @Title: getWaitSendOrder
	 * @Description: 获取待配送的订单，按照送餐时间排序
	 * @author 孟凡岭
	 * @param @param id
	 * @param @param statusList
	 * @param @param pageNum
	 * @param @param pageNumOffset 如果pageNumOffset偏移量大于1，则需要从第一条返回(pageNumOffset
	 *        * pageSize)条数据
	 * @param @param pageSize
	 * @param @return
	 * @return List<OrderVO> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public List<OrderVO> getWaitSendOrder(String id, List<Integer> statusList, int pageNum, int pageNumOffset, int pageSize) {
		// TODO Auto-generated method stub
		String status = StringUtils.join(statusList.toArray(), ",");
		String sql = "from OrderVO where deliveryman_id=:id and  status in (" + status + ") order by askSendTime asc";
		Query query = this.getSession().createQuery(sql);
		query.setParameter("id", id);
		if (pageNumOffset > 1) {
			query.setFirstResult(0);
			query.setMaxResults(pageNumOffset * pageSize);
		} else {
			query.setFirstResult((pageNum - 1) * pageSize);
			query.setMaxResults(pageSize);
		}
		return query.list();
	}

	/**
	 * @param pageSize
	 * @param pageNum
	 *
	 * @Title: getTheDaySuccessOrderDetail
	 * @Description: 获取当天帐单详情
	 * @author 孟凡岭
	 * @param @param id
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> getTheDaySuccessOrderDetail(String id, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		int start = (pageNum - 1) * pageSize;
		StringBuffer sql = new StringBuffer("select o.id,o.code,date_format(o.status_change_time,'%Y-%m-%d %H:%m') as time,o.Order_Total_Price-o.Take_Meal_Price as price");
		sql.append(" from or_order o where o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + "  and o.pay_method=2 ");
		sql.append(" and date_format(o.status_change_time,'%Y-%m-%d')='" + this.getSystemDateForStr("yyyy-MM-dd") + "' ");
		sql.append(" and o.deliveryman_id=? order by o.status_change_time desc ");
		sql.append(" limit " + start + "," + pageSize);
		return this.getJdbcTemplate().queryForList(sql.toString(), id);
	}

	/*
	 * 根据return_id字段得到归班单号下的所有订单
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getOrderList(String id, int pageNum, int pageSize) {
		// OrderVO orderVO = new OrderVO();
		StringBuffer allFromSql = new StringBuffer("from OrderVO ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from OrderVO ");
		allFromSql.append(" where return_id='").append(id).append("'");
		countFromSql.append(" where return_id='").append(id).append("'");
		Map<String, Object> map = new HashMap<String, Object>();
		Query queryCount = this.getSession().createQuery(countFromSql.toString());

		Integer count = ((Long) queryCount.uniqueResult()).intValue();

		map.put("count", count);

		String sql = allFromSql.toString();

		List<OrderVO> orderList = this.getSession().createQuery(sql).setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list();

		map.put("list", orderList);
		return map;
	}

	/**
	 *
	 * @Title: queryWaitOrderByStoIdAndResId
	 * @Description: 通过店面ID和餐厅ID查询出未完成的订单
	 * @author 孟凡岭
	 * @param @param storeIds
	 * @param @param restaurantId
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> queryWaitOrderByStoIdAndResId(String storeIds, String restaurantIds) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("select count(o.id) as count,store.sto_name,res.res_name ");
		sql.append(" from or_order o INNER JOIN bd_storefront store ");
		sql.append(" on store.id=o.storefront_id ");
		if (StringUtils.isNotBlank(storeIds)) {
			sql.append(" and store.id in (" + storeIds + ") ");
		}
		sql.append(" INNER JOIN bd_restaurant res on res.id=store.restaurant_id ");
		sql.append(" and res.id in (" + restaurantIds + ") ");
		sql.append(" where o.status<>8 GROUP BY store.sto_name,res.res_name");
		return this.getJdbcTemplate().queryForList(sql.toString());
	}

	/**
	 *
	 * @Title: getOrderVOListByStatusAndDate4APP
	 * @Description: APP分页查询订单
	 * @author 孟凡岭
	 * @param @param deliveryManId
	 * @param @param statusList
	 * @param @param pageNum 第几页
	 * @param @param pageNumOffset 如果pageNumOffset偏移量大于1，则需要从第一条返回(pageNumOffset
	 *        * pageSize)条数据
	 * @param @param pageSize 每页多少条
	 * @param @param startDate
	 * @param @param endDate
	 * @param @return
	 * @return List<OrderVO> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public List<OrderVO> getOrderVOListByStatusAndDate4APP(String deliveryManId, List<Integer> statusList, int pageNum, int pageNumOffset, int pageSize, Date startDate, Date endDate) {
		StringBuffer sql = new StringBuffer("from OrderVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		for (Integer status : statusList) {
			set.add(status);
		}
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		if (StringUtils.isNotEmpty(deliveryManId)) {
			sql.append(" and deliveryman_id='" + deliveryManId + "'");
		}
		if ((null != startDate) && (null != endDate)) {
			SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
			sql.append(" and date_format(status_change_time,'%Y-%m-%d') between '" + sd.format(startDate) + "' and '" + sd.format(endDate) + "'");
		}

		sql.append(" order by complete_Time desc ");
		Query query = this.getSession().createQuery(sql.toString());
		if ((pageNum != 0) && (pageSize != 0)) {
			/**
			 * 如果pageNumOffset偏移量大于1，则需要从第一条返回(pageNumOffset * pageSize)条数据
			 */
			if (pageNumOffset > 1) {
				query.setFirstResult(0);
				query.setMaxResults(pageNumOffset * pageSize);
			} else {
				query.setFirstResult((pageNum - 1) * pageSize);
				query.setMaxResults(pageSize);
			}
		}
		List<OrderVO> orderList = query.list();
		return orderList;
	}

	/**
	 * @param pageSize
	 * @param pageNum
	 *
	 * @Title: getAlreadyMoneyAndOrderList
	 * @Description: 获取已经缴纳金钱的订单列表
	 * @author 孟凡岭
	 * @param @param manId
	 * @param @return
	 * @return List<Map<String, Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> getAlreadyMoneyAndOrderList(String manId, int pageNum, int pageSize) {
		int start = (pageNum - 1) * pageSize;
		StringBuffer sql = new StringBuffer("select o.id,o.code,date_format(o.status_change_time,'%Y-%m-%d %H:%m') as time,o.Order_Total_Price-o.Take_Meal_Price as price");
		sql.append(" from or_order o where o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + " and o.pay_method=2 ");
		sql.append(" and date_format(o.status_change_time,'%Y-%m-%d')='" + this.getSystemDateForStr("yyyy-MM-dd") + "' ");
		sql.append(" and o.deliveryman_id=? order by o.status_change_time desc ");
		sql.append(" limit " + start + "," + pageSize);
		return this.getJdbcTemplate().queryForList(sql.toString(), manId);
	}

	/**
	 *
	 * @Title: delive
	 * @Description: 分配派送人员
	 * @author 孟凡岭
	 * @param @param list
	 * @param @param manId
	 * @return void 返回类型
	 * @throws
	 */
	public void delive(final List<String> list, final String manId, final String name,final int status, final int postmanid, final String deliverymanphone) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("UPDATE or_order o set o.status=" + status + " ,o.status_change_time='" + this.getSystemDateForStr("yyyy-MM-dd HH:mm") + "'").append(" ,o.update_date='" + this.getSystemDateForStr("yyyy-MM-dd HH:mm") + "'");
		sql.append(" ,o.ready_Status=1,o.deliveryman_id=?,deliveryman_name=?,postman_id=?,deliveryman_phone=? where o.id=? ");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, manId);
				ps.setString(2, name);
				ps.setInt(3, postmanid);
				ps.setString(4, deliverymanphone);
				
				ps.setString(3, list.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return list.size();
			}
		});
	}
	
	/**
	 * @param status
	 *
	 * @Title: queryByIds
	 * @Description: 通过主键集合查询出未分配订单
	 * @author 孟凡岭
	 * @param @param ids
	 * @param @param status
	 * @param @return
	 * @return List<String> 返回类型
	 * @throws
	 */
	public List<String> queryNoDeliveByIds(String[] ids, String[] status) {
		// TODO Auto-generated method stub
		List<String> list = new ArrayList<String>();
		String s = StringUtils.join(status, ",");
		for (int i = 0; i < ids.length; i++) {
			list.add("'" + ids[i] + "'");
		}
		String id = StringUtils.join(list, ",");
		String sql = "select id from or_order where id in(" + id + ") and status in (" + s + ")";
		return this.getJdbcTemplate().queryForList(sql, String.class);
	}

	/**
	 *
	 * @Title: storeAcceptGetOrderList
	 * @Description: 店面受理查询订单
	 * @author 孟凡岭
	 * @param @param orderVO
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> storeAcceptGetOrderList(OrderVO orderVO, int pageNum, int pageSize) {
		StringBuffer allFromSql = new StringBuffer("from OrderVO ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from OrderVO ");
		StringBuffer whereSql = new StringBuffer();
		/**
		 * 条件参数
		 */
		List<Object> param = new ArrayList<Object>();
		if (null != orderVO) {
			whereSql.append("where");
		}
		if (StringUtils.isNotBlank(orderVO.getCode())) {
			whereSql.append(" and code like ? ");
			param.add("%" + orderVO.getCode() + "%");
		}
		if (StringUtils.isNotBlank(orderVO.getDeliveryManName())) {
			whereSql.append(" and deliveryMan.delName like ? ");
			param.add("%" + orderVO.getDeliveryManName() + "%");
		}
		if (orderVO.getStatus() != 0) {
			whereSql.append(" and status=? ");
			param.add(orderVO.getStatus());
		}
		if (StringUtils.isNotBlank(orderVO.getCustName())) {
			whereSql.append(" and cust_name like ? ");
			param.add("%" + orderVO.getCustName() + "%");
		}
		if (orderVO.getPayMethod() != 0) {
			whereSql.append(" and pay_method=? ");
			param.add(orderVO.getPayMethod());
		}
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
		if (orderVO.getCreateDate() != null) {
			whereSql.append(" and date_format(create_date,'%Y-%m-%d')='").append(sd.format(orderVO.getCreateDate())).append("'");
		}
		if (orderVO.getAskSendTime() != null) {
			whereSql.append(" and date_format(ask_send_time,'%Y-%m-%d')='").append(sd.format(orderVO.getAskSendTime())).append("'");
		}

		Map<String, Object> map = new HashMap<String, Object>();
		Query queryCount = this.getSession().createQuery(this.processWhere(countFromSql.append(whereSql).toString()));
		String sql = this.processWhere(allFromSql.append(whereSql).toString());
		Query query = this.getSession().createQuery(sql);
		/**
		 * 遍历条件参数
		 */
		for (int i = 0; i < param.size(); i++) {
			queryCount.setParameter(i, param.get(i));
			query.setParameter(i, param.get(i));
		}
		query.setFirstResult((pageNum - 1) * pageSize);
		query.setMaxResults(pageSize);
		Integer count = ((Long) queryCount.uniqueResult()).intValue();
		map.put("count", count);
		List<OrderVO> orderList = query.list();
		map.put("list", orderList);
		return map;
	}

	/**
	 *
	 * @Title: cancelOrder
	 * @Description: 取消订单
	 * @author 孟凡岭
	 * @param @param ids
	 * @param @param outCauseDesc
	 * @param @param insideCauseDesc
	 * @return void 返回类型
	 * @throws
	 */
	public void cancelOrder(final String[] ids, int status, final String outCauseDesc, final String insideCauseDesc) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("update or_order set status=" + status + ",Out_Cause_Desc=?,Inside_Cause_Desc=? ");
		sql.append(" ,ready_Status=0,status_change_time='" + this.getSystemDateForStr("yyyy-MM-dd HH:mm") + "' where id=? ");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, outCauseDesc);
				ps.setString(2, insideCauseDesc);
				ps.setString(3, ids[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return ids.length;
			}
		});
	}

	/**
	 *
	 * @Title: makeFoodSuccess
	 * @Description: 订单备单完成
	 * @author 孟凡岭
	 * @param @param ids
	 * @return void 返回类型
	 * @throws
	 */
	public void makeFoodSuccess(final String[] ids) {
		String sql = "update or_order set ready_Status=2 where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, ids[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return ids.length;
			}
		});
	}

	/**
	 *
	 * @Title: startMakeFood
	 * @Description: 开始备单
	 * @author 孟凡岭
	 * @param @param ids
	 * @return void 返回类型
	 * @throws
	 */
	public void startMakeFood(final String[] ids) {
		// String sql =
		// "update or_order set status=8,ready_Status=1 ,status_change_time='" +
		// this.getSystemDateForStr("yyyy-MM-dd HH:mm") + "' where id=?";
		String sql = "update or_order set ready_Status=1 ,status_change_time='" + this.getSystemDateForStr("yyyy-MM-dd HH:mm") + "' where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, ids[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return ids.length;
			}
		});
	}

	/**
	 * @param pageSize
	 * @param pageNum
	 * @param returnRecordId
	 * @param returnDate
	 *
	 * @Title: getReturnSettleDetail
	 * @Description: 归班反馈中，获取指定配送员当天成功的订单
	 * @author 孟凡岭
	 * @param @param deliveryManId
	 * @param @return
	 * @return Map<String, Object> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getReturnSettleDetail(int pageNum, int pageSize, String deliveryManId, String returnDate, String returnRecordId, int status) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("from OrderVO o ");
		StringBuffer where = new StringBuffer("where 1=1 and o.status=" + status);
		where.append(" and o.deliveryMan.id=? and DATE_FORMAT(complete_Time,'%Y-%m-%d')='" + returnDate + "' ");

		if (StringUtils.isNotBlank(returnRecordId)) {
			where.append(" and o.returnRecordId='" + returnRecordId + "'");
		}
		where.append(" and pay_method=2 ");
		Query query = this.getSession().createQuery(sql.append(where).toString());
		query.setParameter(0, deliveryManId);
		query.setFirstResult(pageNum * pageSize);
		query.setMaxResults(pageSize);
		sql.setLength(0);
		sql.append("select count(*) from OrderVO o ").append(where);
		int count = Integer.parseInt(this.getSession().createQuery(sql.toString()).setParameter(0, deliveryManId).uniqueResult().toString());
		List<OrderVO> list = query.list();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("count", count);
		map.put("list", list);
		/**
		 * 计算总页数，返回前台
		 */
		if ((count % pageSize) > 0) {
			map.put("page_total_num", (count / pageSize) + 1);
		} else {
			map.put("page_total_num", count / pageSize);
		}
		return map;
	}

	/**
	 *
	 * @Title: storeFrontAccept
	 * @Description: 门店接受订单
	 * @author 孟凡岭
	 * @param @param ids
	 * @return void 返回类型
	 * @throws
	 */
	public void storeFrontAccept(final String[] ids) {
		// TODO Auto-generated method stub
		String sql = "update or_order set status=11 ,status_change_time='" + this.getSystemDateForStr("yyyy-MM-dd HH:mm") + "' where id=?";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, ids[i]);
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return ids.length;
			}
		});
	}

	/**
	 *
	 * @Title: countDeliveryCharge
	 * @Description: 统计派送费用
	 * @author 孟凡岭
	 * @param @param deliveryManId
	 * @param @param startTime
	 * @param @param endTime
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	public Map<String, Object> countDeliveryCharge(String deliveryManId, String startTime, String endTime, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Map<String, Object> map = new HashMap<String, Object>();
		int start = (pageNum - 1) * pageSize;
		/**
		 * 查询统计
		 */
		StringBuffer sql = new StringBuffer("select o.deliveryman_id,man.del_name,DATE_FORMAT(o.complete_Time,'%Y-%m-%d') as 'completeTime' ");
		sql.append(" ,sum(case when (o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue() + ") then '1' else 0 end) as 'success' ");
		sql.append(" ,sum(case o.status when " + OrderStatusEnum.REFUSE_RECEIVE.getValue() + " then '1' else 0 end) as 'fail' ");
		sql.append(" from or_order o,bd_deliveryman man where o.deliveryman_id=man.id ");
		sql.append(" and DATE_FORMAT(o.complete_Time,'%Y-%m-%d') between '" + startTime + "' and '" + endTime + "'");
		sql.append(" and o.is_Settle_Delivery=0 and pay_method=2 ");
		if (StringUtils.isNotBlank(deliveryManId)) {
			sql.append(" and o.deliveryman_id='" + deliveryManId + "'");
		}
		sql.append(" group by o.deliveryman_id,man.del_name,DATE_FORMAT(o.complete_time,'%Y-%m-%d') ");
		StringBuffer countSql = new StringBuffer();
		/**
		 * 子查询统计总数
		 */
		countSql.append("select count(*) from (" + sql + ") as temp ");

		sql.append(" limit " + start + "," + pageSize);
		List<Map<String, Object>> result = this.getJdbcTemplate().queryForList(sql.toString());
		int count = this.getJdbcTemplate().queryForInt(countSql.toString());
		map.put("list", result);
		map.put("count", count);
		return map;
	}

	/**
	 *
	 * @Title: getSettleDetail
	 * @Description: 获取派费详情
	 * @author 孟凡岭
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @param deliveryManId
	 * @param @param returnDate
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getSettleDetail(int pageNum, int pageSize, String deliveryManId, String returnDate) {
		StringBuffer sql = new StringBuffer("from OrderVO o ");
		StringBuffer where = new StringBuffer(" where  o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue());
		where.append(" and o.deliveryMan.id=? and DATE_FORMAT(complete_Time,'%Y-%m-%d')='" + returnDate + "' ");
		where.append(" and o.isSettleDelivery=0 and pay_method=2 ");
		Query query = this.getSession().createQuery(sql.append(where).toString());
		query.setParameter(0, deliveryManId);
		query.setFirstResult(pageNum * pageSize);
		query.setMaxResults(pageSize);
		List<OrderVO> list = query.list();
		sql.setLength(0);
		sql.append("select count(*) from OrderVO o ").append(where);
		int count = Integer.parseInt(this.getSession().createQuery(sql.toString()).setParameter(0, deliveryManId).uniqueResult().toString());
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("count", count);
		map.put("list", list);
		/**
		 * 计算总页数，返回前台
		 */
		if ((count % pageSize) > 0) {
			map.put("page_total_num", (count / pageSize) + 1);
		} else {
			map.put("page_total_num", count / pageSize);
		}
		return map;
	}

	/**
	 *
	 * @Title: getRecordDetail
	 * @Description: 通过派费历史记录查看详情
	 * @author 孟凡岭
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @param deliveryManId
	 * @param @param returnDate
	 * @param @param returnRecordId
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	public Map<String, Object> getRecordDetail(int pageNum, int pageSize, String deliveryManId, String returnDate, String returnRecordId) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("from OrderVO o ");
		StringBuffer where = new StringBuffer("where 1=1 and o.status=" + OrderStatusEnum.SIGN_RECEIVE.getValue());
		where.append(" and o.deliveryMan.id=? and DATE_FORMAT(complete_Time,'%Y-%m-%d')='" + returnDate + "' ");
		where.append(" and o.settleRecordId='" + returnRecordId + "' and o.isSettleDelivery=1 and pay_method=2");

		Query query = this.getSession().createQuery(sql.append(where).toString());
		query.setParameter(0, deliveryManId);
		query.setFirstResult(pageNum * pageSize);
		query.setMaxResults(pageSize);
		sql.setLength(0);
		sql.append("select count(*) from OrderVO o ").append(where);
		int count = Integer.parseInt(this.getSession().createQuery(sql.toString()).setParameter(0, deliveryManId).uniqueResult().toString());
		@SuppressWarnings("unchecked")
		List<OrderVO> list = query.list();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("count", count);
		map.put("list", list);
		/**
		 * 计算总页数，返回前台
		 */
		if ((count % pageSize) > 0) {
			map.put("page_total_num", (count / pageSize) + 1);
		} else {
			map.put("page_total_num", count / pageSize);
		}
		return map;
	}

	/**
	 *
	 * @Title: getByIdAndDeliveryManId
	 * @Description: 通过订单号和配送员ID查询订单
	 * @author 孟凡岭
	 * @param @param orderId
	 * @param @param deliveryManId
	 * @param @return
	 * @return OrderVO 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public OrderVO getByIdAndDeliveryManId(String orderId, String deliveryManId) {
		// TODO Auto-generated method stub
		String sql = "from OrderVO o where o.id=? and o.deliveryMan.id=? ";
		Query query = this.getSession().createQuery(sql);
		query.setParameter(0, orderId);
		query.setParameter(1, deliveryManId);
		List<OrderVO> list = query.list();
		if (list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 *
	 * @Title: getIdByStatusAndId
	 * @Description: 根据订单状态和ID集合来获得订单号列表
	 * @author 孟凡岭
	 * @param @param ids 订单ID
	 * @param @param status 订单状态
	 * @param @param readyStatus 备单状态 0:未备单，1：开始备单，2：备单完成，如果为10表示不进行此条件判断
	 * @param @return
	 * @return List<String> 返回类型
	 * @throws
	 */
	public List<String> getIdByStatusAndId(String[] ids, String[] status, int readyStatus) {
		// TODO Auto-generated method stub
		String s = StringUtils.join(status, ",");
		String idss = StringUtils.join(ids, ",");
		String sql = "select id from or_order where status in(" + s + ") and id in(" + idss + ") ";
		if (readyStatus != 10) {
			sql += " and ready_Status=" + readyStatus;
		}
		return this.getJdbcTemplate().queryForList(sql, String.class);
	}
	
	/**
	 * 
	* @Title: getNotFinishOrderbyDeliveryManid 
	* @Description: 查询一个配送人员是否还有未完成的订单
	* @author 小强
	* @param @param deliverymanid
	* @param @return    设定文件 
	* @return List<OrderVO>    返回类型 
	* @throws
	 */
	@SuppressWarnings("unchecked")
	public List<OrderVO> getNotFinishOrderbyDeliveryManid(String deliverymanid){
		String orderstatusBuffer = "(" + OrderStatusEnum.TO_DISTRIBUTE.getValue() + "," + OrderStatusEnum.RECEIVE_ORDER.getValue() + "," + OrderStatusEnum.READY_DILIVERY.getValue()+")";
		StringBuffer sql = new StringBuffer("from OrderVO o where status in" + orderstatusBuffer + " and  deliveryman_id=:id ");
		Query query = this.getSession().createQuery(sql.toString());
		query.setParameter("id", deliverymanid);	
		return query.list();		
	}
}
